package com.example.demo1.generator.codedesign.service;

import com.example.demo1.generator.codedesign.CommonUtil;
import com.example.demo1.generator.codedesign.JdbcUtil;
import com.example.demo1.generator.codedesign.model.Column;
import com.example.demo1.generator.codedesign.model.FormType;
import com.example.demo1.generator.codedesign.model.PropertyType;
import com.example.demo1.generator.codedesign.model.Table;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

@Service
public class SchemaService {

	private Logger logger = LoggerFactory.getLogger(getClass());

	@Resource
	private DataSource dataSource;

	private Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage(), e);
		}
	}

	/**
	 * 获取当前连接的所有表名称
	 * 
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	private List<String> getTableNames() throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		List<String> list = new ArrayList<String>();
		try {
			conn = getConnection();
			String catalog = conn.getCatalog();
			DatabaseMetaData metaData = conn.getMetaData();
			rs = metaData.getTables(catalog, null, null,
					new String[] { "TABLE" });
			while (rs.next()) {
				list.add(rs.getString("TABLE_NAME"));
			}
			return list;
		} finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(conn);
		}
	}

	/**
	 * 获取列的基本信息
	 * 
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private List<Column> getColumnList(String tableName) throws SQLException {
		Connection conn = null;
		ResultSet keySet = null;
		PreparedStatement statement = null;
		Set<String> primaryKeySet = new HashSet<>();
		List<Column> list = new ArrayList<>(500);
		try {
			conn = getConnection();
			String catalog = conn.getCatalog();
			String sql = "select * from  " + catalog + "." + tableName
					+ " limit 0";
			statement = conn.prepareStatement(sql);
			// 获取当前表的元数据
			ResultSetMetaData metaData = statement.executeQuery().getMetaData();
			// 获取当前表的主键列表
			keySet = conn.getMetaData().getPrimaryKeys(null, null, tableName);
			while (keySet.next()) {
				primaryKeySet.add(keySet.getString("COLUMN_NAME"));
			}
			Map<String, String> columnComments = getColumnComment(tableName);
			for (int i = 1, len = metaData.getColumnCount(); i <= len; i++) {
				Column item = new Column();
				item.setName(metaData.getColumnName(i));
				// 设置是否在页面显示
				item.setShow(showColumn(item.getName()));
				item.setSqlType(metaData.getColumnType(i));
				item.setLenght(metaData.getColumnDisplaySize(i));
				item.setNullable(metaData.isNullable(i) == 1);
				if (primaryKeySet.contains(item.getName())) {
					item.setPrimaryKey(true);
				} else {
					item.setPrimaryKey(false);
				}
				item.setReadOnly(metaData.isReadOnly(i));
				item.setSearchable(metaData.isSearchable(i));
				item.setWritable(metaData.isWritable(i));
				item.setComment(columnComments.get(item.getName()));

				PropertyType propertyType = PropertyType.getJavaType(item
						.getSqlType());
				FormType formType = FormType.transform(propertyType);
				item.setFormType(formType);
				item.setPropertyType(propertyType);

				// 如果超过128字符使用text area
				if (item.getLenght() >= 256
						&& item.getPropertyType() == PropertyType.STRING) {
					item.setFormType(FormType.TEXTAREA);
					item.setPropertyType(PropertyType.STRING);
				}

				if (StringUtils.isEmpty(item.getComment())) {
					item.setComment(null);
				}
				item.setPropertyName(CommonUtil.underlineToCapitalize(item
						.getName()));
				item.setPropertyNameUpper(StringUtils.capitalize(item
						.getPropertyName()));

				item.setConstantName(CommonUtil.capitalizeToUpperUnderline(item
						.getName()));
				
				list.add(item);
			}
			return list;
		} finally {
			JdbcUtil.close(keySet);
			JdbcUtil.close(statement);
			JdbcUtil.close(conn);
		}
	}

	/**
	 * 获取表的注释信息
	 * 
	 * @return
	 * @throws SQLException
	 */
	private Map<String, String> getTableComment() throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		Map<String, String> map = new HashMap<>();
		String sql = "select t.TABLE_NAME,t.TABLE_COMMENT from information_schema.TABLES t where t.TABLE_SCHEMA = ?";
		try {
			conn = getConnection();
			String catalog = conn.getCatalog();
			rs = JdbcUtil.executeQuery(sql, conn, catalog);
			while (rs.next()) {
				String tname = rs.getString("TABLE_NAME");
				String tcomment = rs.getString("TABLE_COMMENT");
				map.put(tname, tcomment);
			}
			return map;
		} finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(conn);
		}
	}

	/**
	 * 获取列字段的注释信息
	 * 
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private Map<String, String> getColumnComment(String tableName)
			throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		Map<String, String> map = new HashMap<>();
		String sql = "select t.COLUMN_NAME,t.COLUMN_COMMENT from information_schema.COLUMNS t where t.TABLE_SCHEMA = ? and t.TABLE_NAME = ?";
		try {
			conn = getConnection();
			String catalog = conn.getCatalog();
			rs = JdbcUtil.executeQuery(sql, conn, catalog, tableName);
			while (rs.next()) {
				String cname = rs.getString("COLUMN_NAME");
				String tcomment = rs.getString("COLUMN_COMMENT");
				map.put(cname, tcomment);
			}
			return map;
		} finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(conn);
		}
	}

	/**
	 * 获取数据表结构
	 * 
	 * @return
	 */
	public List<Table> getTables() throws SQLException {
		List<Table> tables = new ArrayList<>(1000);
		List<String> tableNames = getTableNames();
		Map<String, String> tableComments = getTableComment();
		Connection conn = null;
		String catalog = null;
		try {
			conn = getConnection();
			catalog = conn.getCatalog();
		} finally {
			JdbcUtil.close(conn);
		}
		String nameSeparator = "_";
		for (String tableName : tableNames) {
			try {

				List<Column> columns = getColumnList(tableName);
				Table table = new Table();
				String[] nameSplits = StringUtils.split(tableName,
						nameSeparator);
				String moduleName = nameSplits[0];
				String className = nameSplits[nameSplits.length - 1];
				// 下划线转首字母大写
				className = CommonUtil.underlineToCapitalize(className);
				className = StringUtils.capitalize(className);
				if (nameSplits.length >= 3) {
					table.setFunctionName(nameSplits[1]);
					className = CommonUtil.arrayToCapitalize(Arrays.copyOfRange(nameSplits, 2, nameSplits.length));
				}

				table.setName(tableName);
				table.setColumns(columns);
				table.setComment(tableComments.get(tableName));
				table.setSchema(catalog);

				table.setModuleName(moduleName);
				table.setClassName(className);
				table.setLowerClassName(StringUtils.uncapitalize(table
						.getClassName()));

				moduleName = StringUtils.lowerCase(moduleName);
				String requestPath = "/" + moduleName + "/"
						+ StringUtils.lowerCase(className);
				table.setRequestPath(requestPath);
				// 设置主键字段
				columns.forEach(item -> {
					if (item.isPrimaryKey()) {
						table.setIdColumn(item);
					}
				});
				// 删除主键字段
				columns.remove(table.getIdColumn());
				tables.add(table);

				logger.info("Get table :"
						+ ToStringBuilder.reflectionToString(table,
								ToStringStyle.SHORT_PREFIX_STYLE));
			} catch (Exception e) {
				logger.error(e.getMessage(), e);
			}
		}
		return tables;
	}
	
	private boolean showColumn(String columnName) {
		return "creator, ctime, modifier, utime".indexOf(columnName) == -1;
	}

}
